package algorithm.model.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import algorithm.model.vo.Team;
import algorithm.model.vo.TeamFinal;
import algorithm.util.DBUtil;

public class TeamDao {

	public ArrayList<Team> list() throws Exception {
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String sql = "select * FROM TEAMS";
		ArrayList<Team> teams = new ArrayList<Team>();

		try {
			con = DBUtil.getConnection();
			stmt = con.prepareStatement(sql);

			rs = stmt.executeQuery();

			while (rs.next()) {
				teams.add(new Team(
						rs.getString("id"), rs.getString("password"), 
						rs.getString("name"), rs.getString("classGroup"), 
						rs.getString("className"), rs.getString("member1"), 
						rs.getString("member2"), rs.getString("member3"), 
						rs.getString("introduce"), rs.getInt("curNum")));
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
			DBUtil.close(rs);
			DBUtil.close(stmt);
			DBUtil.close(con);
		}
		return teams;
	}
	
	public ArrayList<TeamFinal> finalList() throws Exception {
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		ArrayList<TeamFinal> teamfs = new ArrayList<TeamFinal>();
		String sql = " select t.id, t.name, t.classgroup, t.classname, t.member1, t.member2, t.member3, "
			       + " t.curnum, re.t_score, re.t_time "  
			       + " from teams t, ( " 
			       + " select r.id, sum(round(to_char(r.endtime - r.starttime)* 60*60*24)) t_time, sum(m.score)- sum(r.tryCount) * 0.5 t_score " 
			       + " from records r, answers a , missions m " 
			       + " where 1=1 " 
			       + " and r.id = a.id " 
			       + " and r.num = a.num " 
			       + " and r.num = m.num " 
			       + " and a.subanswer != 'giveup' " 
			       + " group by r.id ) re " 
			       + " where 1=1  " 
			       + " and t.id = re.id order by t_score desc, t_time asc ";

		try {
			con = DBUtil.getConnection();
			stmt = con.prepareStatement(sql);

			rs = stmt.executeQuery();

			while (rs.next()) {
				teamfs.add(new TeamFinal(rs.getString("id"), "", 
						rs.getString("name"), rs.getString("classgroup"), 
						rs.getString("classname"), rs.getString("member1"), 
						rs.getString("member2"), rs.getString("member3"), 
						"", rs.getInt("curnum"), 
						rs.getString("t_time") ,rs.getInt("t_score")));
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
			DBUtil.close(rs);
			DBUtil.close(stmt);
			DBUtil.close(con);
		}
		return teamfs;
	}
	

	public Team search(String id) throws Exception {
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String sql = "SELECT * FROM teams WHERE id=?";

		try {
			con = DBUtil.getConnection();
			stmt = con.prepareStatement(sql);
			stmt.setString(1, id);

			rs = stmt.executeQuery();

			if (rs.next()) {
				return new Team(rs.getString("id"), rs.getString("password"),
						rs.getString("name"), rs.getString("classGroup"),
						rs.getString("className"), rs.getString("member1"),
						rs.getString("member2"), rs.getString("member3"),
						rs.getString("introduce"), rs.getInt("summitCount"));
			} else {
				return null;
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
			DBUtil.close(rs);
			DBUtil.close(stmt);
			DBUtil.close(con);
		}
	}

	public boolean register(Team team) throws Exception {
		Connection con = null;
		PreparedStatement stmt = null;
		String sql = "insert into TEAMS values(?, ?, ?, ?, ?, ?, ?, ?, ?, 1)";

		try {
			con = DBUtil.getConnection();
			stmt = con.prepareStatement(sql);
			stmt.setString(1, team.getId());
			stmt.setString(2, team.getPassword());
			stmt.setString(3, team.getName());
			stmt.setString(4, team.getClassGroup());
			stmt.setString(5, team.getClassName());
			stmt.setString(6, team.getMember1());
			stmt.setString(7, team.getMember2());
			stmt.setString(8, team.getMember3());
			stmt.setString(9, team.getIntroduce());

			stmt.executeUpdate();
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			DBUtil.close(stmt);
			DBUtil.close(con);
		}
	}

	public void modify(String id, String password) throws Exception {
		Connection con = null;
		PreparedStatement stmt = null;
		String sql = "update TEAMS set password=? where id=? ";

		try {
			con = DBUtil.getConnection();
			stmt = con.prepareStatement(sql);
			stmt.setString(1, password);
			stmt.setString(2, id);

			stmt.executeUpdate();

		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
			DBUtil.close(stmt);
			DBUtil.close(con);
		}
	}

	public void plusNum(String id) throws Exception {
		Connection con = null;
		PreparedStatement stmt = null;
		String sql = "update TEAMS set curNum= curNum+1 where id=? ";

		try {
			con = DBUtil.getConnection();
			stmt = con.prepareStatement(sql);
			stmt.setString(1, id);

			stmt.executeUpdate();

		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
			DBUtil.close(stmt);
			DBUtil.close(con);
		}
	}

	public void adminReset() throws Exception {
		Connection con = null;
		PreparedStatement stmt = null;
		String sql = " update TEAMS set curNum=1 where id = 'admin' ";

		try {
			con = DBUtil.getConnection();
			stmt = con.prepareStatement(sql);

			stmt.executeUpdate();

		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
			DBUtil.close(stmt);
			DBUtil.close(con);
		}
	}

}
